/*----------------------------------------------------------------
// Copyright (C) 2011  博览财经资讯服务有限公司
// 版权所有
// 文件名：DBHelper
// 文件功能描述：通用数据访问类
// 
// 创建标识:王家新
// 
// 修改标识：王家新 2012-5-18
// 修改描述：将静态类DBHelper重构为对象类
//         
// 修改标识：
// 修改描述：
//----------------------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Text.RegularExpressions;

namespace Aij1.Utils
{
    /// <summary>
    /// 通用数据访问类
    /// </summary>
    public class DBHelper
    {
        static string defaultConnStr;
        /// <summary>
        /// 默认连接串
        /// connectionStrings配置节最前面要加上&lt;clear/&gt;标记确保能读到序号为0的连接串
        /// </summary>
        public static String DefaultConnStr
        {
            get
            {
                if (String.IsNullOrEmpty(defaultConnStr))
                {
                    defaultConnStr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
                }
                return defaultConnStr;
            }
            set
            {
                defaultHelper = null;
                defaultConnStr = value;
            }
        }

        static DBHelper defaultHelper;

        public static IDBComm Comm { get { return DefaultHelper.DBComm; } }
        /// <summary>
        /// 默认帮助类，用数据库第一个连接串构建。
        /// connectionStrings配置节最前面要加上&lt;clear/&gt;标记确保能读到序号为0的连接串
        /// </summary>
        public static DBHelper DefaultHelper
        {
            get
            {
                if (defaultHelper == null)
                {
                    defaultHelper = new DBHelper(DefaultConnStr);
                }
                return defaultHelper;
            }
        }

        DbTransaction trans;
        DbConnection TransConn;

        public string ConnStr { get; set; }

        IDBComm dBComm;
        IDBComm DBComm
        {
            get
            {
                if (dBComm == null)
                {
                    dBComm = DBCommCreater.CreateDBComm(ConnStr);
                    dBComm.Helper = this;
                }
                return dBComm;
            }
        }

        int Timeout { get; set; }

        Dictionary<string, string> versions = new Dictionary<string, string>();

        public DBHelper(string connStr)
        {
            ConnStr = connStr;
        }

        public String GetServerVersion()
        {
            if (!versions.ContainsKey("ServerVersion"))
                TestConnetion();
            return versions["ServerVersion"];
        }

        public String GetDBName()
        {
            if (!versions.ContainsKey("Database"))
                TestConnetion();
            return versions["Database"];
        }

        /// <summary>
        /// 测试Connection
        /// </summary>
        /// <returns></returns>
        public bool TestConnetion()
        {
            try
            {
                TransConn = DBComm.CreateConnection();
                TransConn.Open();
                versions["ServerVersion"] = TransConn.ServerVersion;
                versions["Database"] = TransConn.Database;
                versions["DataSource"] = TransConn.DataSource;
            }
            catch
            {
                return false;
            }
            TransConn.Close();
            return true;
        }

        /// <summary>
        /// 以connstr开始一个事务
        /// </summary>
        /// <param name="connstr"></param>
        public void BeginTrans()
        {
            IDBComm dbComm = DBCommCreater.CreateDBComm(ConnStr);
            TransConn = dbComm.CreateConnection();
            TransConn.Open();
            trans = TransConn.BeginTransaction();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTrans()
        {
            trans.Rollback();
            TransConn.Close();
        }

        /// <summary>
        /// 结束事务
        /// </summary>
        /// <returns></returns>
        public String EndTrans()
        {
            String s = "";
            try
            {
                trans.Commit();
            }
            catch (DbException ex)
            {
                trans.Rollback();
                s = ex.Message;
            }

            TransConn.Close();
            return s;
        }

        /// <summary>
        /// 用指定连接串执行非查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connstr"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public int ExecNonQuery(string sql, params DbParameter[] sp)
        {
            using (DbConnection conn = DBComm.CreateConnection())
            {
                DbCommand sc = DBComm.CreateCommand(sql);
                PrepareCommand(sc, sp);
                int r = sc.ExecuteNonQuery();
                conn.Close();
                sc.Parameters.Clear();
                return r;
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        public int RunProcedure(string procname, params DbParameter[] sp)
        {
            using (DbConnection conn = DBComm.CreateConnection())
            {
                DbCommand sc = DBComm.CreateCommand(procname);
                sc.CommandType = CommandType.StoredProcedure;
                PrepareCommand(sc, sp);
                int r = sc.ExecuteNonQuery();
                conn.Close();
                sc.Parameters.Clear();
                return r;
            }
        }

        /// <summary>
        /// 执行存储过程(返回DataSet)
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        public DataSet RunProcedureDs(string procname, params DbParameter[] sp)
        {
            using (DbConnection conn = DBComm.CreateConnection())
            {
                DbCommand sc = DBComm.CreateCommand(procname);
                sc.CommandType = CommandType.StoredProcedure;
                PrepareCommand(sc, sp);

                DbDataAdapter da = DBComm.CreateDataAdapter(sc);
                DataSet ds = new DataSet();
                da.Fill(ds);
                conn.Close();
                sc.Parameters.Clear();
                return ds;
            }
        }

        /// <summary>
        /// 执行事务中的非查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public int TransNonQuery(string sql, params DbParameter[] sp)
        {
            DbCommand sc = DBComm.CreateCommand(sql);
            sc.Connection = TransConn;
            PrepareCommand(sc, sp);
            sc.Transaction = trans;
            int i = sc.ExecuteNonQuery();
            sc.Parameters.Clear();
            return i;
        }

        /// <summary>
        /// 用指定连接串执行SQL查询语句，返回DataTable
        /// </summary>
        /// <param name="sql">要执行的Sql语句</param>
        /// <param name="connstr">连接串</param>
        /// <param name="sp">参数数组</param>
        /// <returns></returns>
        public DataTable ExecDataTable(string sql, params DbParameter[] sp)
        {
            using (DbConnection conn = DBComm.CreateConnection())
            {
                DbCommand sc = DBComm.CreateCommand(sql);
                PrepareCommand(sc, sp);
                DbDataAdapter da = DBComm.CreateDataAdapter(sc);
                DataTable dt = new DataTable();
                da.Fill(dt);

                sc.Parameters.Clear();
                return dt;
            }
        }

        /// <summary>
        /// 用指定连接串执行SQL查询语句，返回DataSet
        /// </summary>
        /// <param name="sql">要执行的Sql语句</param>
        /// <param name="connstr">连接串</param>
        /// <param name="sp">参数数组</param>
        /// <returns>DataSet</returns>
        public DataSet ExecDataSet(string sql, string connstr, params DbParameter[] sp)
        {
            using (DbConnection conn = DBComm.CreateConnection())
            {
                DbCommand sc = DBComm.CreateCommand(sql);
                PrepareCommand(sc, sp);
                DbDataAdapter da = DBComm.CreateDataAdapter(sc);
                DataSet ds = new DataSet();
                da.Fill(ds);
                sc.Parameters.Clear();
                return ds;
            }
        }

        /// <summary>
        /// 在事务中获取单个对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public object TransGetObject(string sql, params DbParameter[] sp)
        {
            object o = null;
            DbCommand sc = DBComm.CreateCommand(sql);
            sc.Connection = TransConn;
            PrepareCommand(sc, sp);
            sc.Transaction = trans;
            o = sc.ExecuteScalar();
            sc.Parameters.Clear();
            return o;
        }


        /// <summary>
        /// 用指定连接串得到单一对象的sql语句
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="connstr">指定连接串</param>
        /// <param name="sp">参数数组</param>
        /// <returns>查询得到的对象</returns>
        public object ExecGetObject(String sql, params DbParameter[] sp)
        {
            object o = 0;
            using (DbConnection conn = DBComm.CreateConnection())
            {
                DbCommand sc = DBComm.CreateCommand(sql);
                PrepareCommand(sc, sp);
                o = sc.ExecuteScalar();
                conn.Close();
                sc.Parameters.Clear();
                return o;
            }
        }

        private void PrepareCommand(DbCommand sc, DbParameter[] sp)
        {
            if (sc.Connection.State != ConnectionState.Open)
                sc.Connection.Open();

            sc.CommandTimeout = Timeout;

            if (sp != null)
            {
                foreach (DbParameter parm in sp)
                    sc.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// 根据指定Sql和指定连接串返回一个SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connStr"></param> 
        /// <param name="sp"></param>
        /// <returns></returns>
        public IDataReader ExecReader(string sql, params DbParameter[] sp)
        {
            DbConnection conn = DBComm.CreateConnection();
            DbCommand sc = DBComm.CreateCommand(sql);

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(sc, sp);
                DbDataReader rdr = sc.ExecuteReader(CommandBehavior.CloseConnection);
                sc.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public IDataReader ExecPageReader(DbPager pager, params DbParameter[] sp)
        {
            string sql = "SELECT COUNT(*) FROM (" + pager.Query + ")c";

            pager.RowNum = (int)ExecGetObject(sql, sp);
            int startRow = pager.PageSize * (pager.PageIndex - 1) + 1;

            sql = String.Format(@"WITH PAGED AS ( 
SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS rowNum, 
* FROM ({1})a)
SELECT TT.*  FROM PAGED P INNER JOIN ({1})TT 
ON P.{2} = TT.{2}  WHERE ROWNUM BETWEEN {3} AND {4}
ORDER BY {0}",
            pager.OrderBy, pager.Query, pager.KeyId, startRow, startRow + pager.PageSize - 1);
            return ExecReader(sql, sp);
        }

        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public DbParameter CreateParameter(string parameterName, object value)
        {
            return DBComm.CreateParameter(parameterName, CommOp.TestNull(value));
        }

        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public DbParameter CreateImageParameter(string parameterName, byte[] value)
        {
            return DBComm.CreateImageParameter(parameterName, value);
        }

        /// <summary>
        /// 获取数据库大小
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public double GetDBSize()
        {
            return DBComm.GetDBSize();
        }

        /// <summary>
        /// 压缩数据库
        /// </summary>
        /// <param name="connStr"></param>
        public void ShrinkDB()
        {
            DBComm.ShrinkDB();
        }
    }

    /// <summary>
    /// 数据库分页对象
    /// </summary>
    public class DbPager
    {
        /// <summary>
        /// 不分页的查询语句
        /// </summary>
        public string Query { get; set; }

        public string OrderBy { get; set; }

        /// <summary>
        /// 主键字段
        /// </summary>
        public string KeyId { get; set; }

        /// <summary>
        /// 页编号
        /// </summary>
        public int PageIndex { get; set; }

        /// <summary>
        /// 页大小
        /// </summary>
        public int PageSize { get; set; }

        /// <summary>
        /// 总记录数
        /// </summary>
        public int RowNum { get; set; }

        /// <summary>
        /// 总页数
        /// </summary>
        public int PageCount
        {
            get { return (RowNum - 1) / PageSize + 1; }
        }
    }
}
